<?php
namespace app\common\base;
use think\Controller;
use think\Db;

/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/9/29
 * Time: 14:44
 */
class Systrade extends Controller{
    /*****************旅客乘车订单*************************/
    /*
     * 列表查询
     * 公共方法
     * 年（每月总数）
     */
    public static function YearItem($where,$where_count,$param_page){
        $param_pagesize = 10;
        $result  = self::MonthList($where,$param_page);
        foreach ( $result as $key => $value){
            $result[$key]['Order'] = $key + 1;
        };
        $data = self::MoneyList($result,$where,$where_count);
        $int_total_count = count(Db::table('order_info')
            ->field(
                [
                    'DATE_FORMAT(a.pay_time, "%Y-%m")'	 =>'PayTime',
                    'COUNT(a.order_id)'	 =>'Count',         //乘车人数
                    'SUM(a.pay_money)'	 =>'PayMoney',      //实付金额
                ]
            )
            ->alias('a')
            ->where($where)
            ->group('PayTime')
            ->order('a.pay_time desc')
            ->select());
        $data['tableData']=$result;
        $data['TotalCount'] = $int_total_count;
        $data['Page'] = (int)$param_page;
        $data['PageSize'] = (int)$param_pagesize;
        return $data;
    }
    /*
     * 列表查询
     * 公共方法
     * 年
     */
    public static function MonthList($where,$param_page,$param_pagesize = 10){
        $where .=' and trade_state = 2';
        $user_result = Db::table('order_info')
            ->field(
                [
                    'DATE_FORMAT(a.pay_time, "%Y-%m")'	 =>'PayTime',
                    'COUNT(a.order_id)'	 =>'Count',         //乘车人数
                    'SUM(a.pay_money)'	 =>'PayMoney',      //实付金额
                ]
            )
            ->alias('a')
            ->where($where)
            ->limit(($param_page - 1) * $param_pagesize, $param_pagesize)
            ->group('PayTime')
            ->order('a.pay_time desc')
            ->select();
        return $user_result;
    }
    /*
     * 列表查询
     * 公共方法
     * 周、月（每天总数）
     */
    public static function WeekItem($where,$where_count,$param_page){
        $param_pagesize = 10;
        $result  = self::WeekList($where,$param_page);
        foreach ( $result as $key => $value){
            $result[$key]['Order'] = $key + 1;
        };
        $data = self::MoneyList($result,$where,$where_count);
        $int_total_count = count(Db::table('order_info')
            ->field(
                [
                    'DATE_FORMAT(a.pay_time, "%Y-%m-%d")'=>'PayTime',
                    'COUNT(a.order_id)'	 =>'Count',     //乘车人数
                    'SUM(a.pay_money)'	 =>'PayMoney',  //实付金额
                ]
            )
            ->alias('a')
            ->where($where)
            ->group('PayTime')
            ->order('a.pay_time desc')
            ->select());
        $data['tableData']=$result;
        $data['TotalCount'] = $int_total_count;
        $data['Page'] = (int)$param_page;
        $data['PageSize'] = (int)$param_pagesize;
        return $data;
    }
    /*
     * 列表查询
     * 公共方法
     * 周、月
     */
    public static function WeekList($where,$param_page,$param_pagesize = 10){
        $where .=' and trade_state = 2';
        $user_result = Db::table('order_info')
            ->field(
                [
                    'DATE_FORMAT(a.pay_time, "%Y-%m-%d")'	 =>'PayTime',
                    'COUNT(a.order_id)'	 =>'Count',     //乘车人数
                    'SUM(a.pay_money)'	 =>'PayMoney',  //实付金额
                ]
            )
            ->alias('a')
            ->where($where)
            ->limit(($param_page - 1) * $param_pagesize, $param_pagesize)
            ->group('PayTime')
            ->order('a.pay_time desc')
            ->select();
        return $user_result;
    }
    /*
     * 列表查询
     * 公共方法
     * 天
     */
    public static function DayItem($where,$where_count,$page=1){
        $param_page = $page;
        $param_pagesize = 10;
        $user_result  = self::DayList('user b','a.openid = b.openid',$where,$param_page);
        /*$staff_result = self::DayList('staff b','a.openid = b.openid',$where,$param_page);
        $external_result = self::DayList('external_staff b','a.openid = b.openid',$where,$param_page);
        $result = array_merge_recursive($user_result,$staff_result,$external_result);
        //重组数组按时间排序
        $ctime_str = array();
        foreach($result as $key=>$v){
            $arr[$key]['PayTime'] = strtotime($v['PayTime']);
            $ctime_str[] = $arr[$key]['PayTime'];
        }
        array_multisort($ctime_str,SORT_DESC,$result);*/
        foreach ( $user_result as $key => $value){
            $user_result[$key]['Order'] = $key + 1;
        };
        $data = self::MoneyList($user_result,$where,$where_count);
        $int_total_count = count(Db::table('order_info')
            ->field(
                [
                    'a.order_id'	     =>'Id',//订单ID
                    'a.out_trade_no'	 =>'OutTradeNo',  //订单流水号
                    'a.pay_time'	     =>'PayTime',     //付款时间
                    'a.pay_money'	     =>'PayMoney',    //实付金额
                    'b.nick_name'        =>'NickName',    //用户
                    'c.type_name'        =>'InvoiceState',//电子发票状态
                    'd.type_name'        =>'InvoiceType',//电子发票抬头类型
                    'e.type_name'        =>'TradeState',
                    'h.line_from'        =>'LineFrom',
                    'h.line_destination' =>'LineDes'
                ]
            )
            ->alias('a')
            ->join('user b','a.openid = b.openid')
            ->join('sys_public_type c','a.invoice_state = c.type_id and c.type_classify = "发票状态"','LEFT')
            ->join('sys_public_type d','a.invoice_type = d.type_id and d.type_classify = "发票类型"','LEFT')
            ->join('sys_public_type e','a.trade_state = e.type_id and e.type_classify = "订单状态"','LEFT')
            ->join('line h','a.line_id = h.line_id','LEFT')
            ->where($where)
            ->order('a.pay_time desc')
            ->select());
        $data['tableData'] = $user_result;
        $data['TotalCount'] = $int_total_count;
        $data['Page'] = (int)$param_page;
        $data['PageSize'] = (int)$param_pagesize;
        return $data;
    }
    /*
     * 订单查询
     */
    public static function DayList($table,$condition,$where,$param_page,$param_pagesize = 10){
        $user_result = Db::table('order_info')
            ->field(
                [
                    'a.order_id'	     =>'Id',//订单ID
                    'a.out_trade_no'	 =>'OutTradeNo',  //订单流水号
                    'a.pay_time'	     =>'PayTime',     //付款时间
                    'a.pay_money'	     =>'PayMoney',    //实付金额
                    'b.nick_name'        =>'NickName',    //用户
                    'c.type_name'        =>'InvoiceState',//电子发票状态
                    'd.type_name'        =>'InvoiceType',//电子发票抬头类型
                    'e.type_name'        =>'TradeState',
                    'h.line_from'        =>'LineFrom',
                    'h.line_destination' =>'LineDes' 
                ]
            )
            ->alias('a')
            ->join($table,$condition)
            ->join('sys_public_type c','a.invoice_state = c.type_id and c.type_classify = "发票状态"','LEFT')
            ->join('sys_public_type d','a.invoice_type = d.type_id and d.type_classify = "发票类型"','LEFT')
            ->join('sys_public_type e','a.trade_state = e.type_id and e.type_classify = "订单状态"','LEFT')
            ->join('line h','a.line_id = h.line_id','LEFT')
            ->where($where)
            ->limit(($param_page - 1) * $param_pagesize, $param_pagesize)
            ->order('a.pay_time desc')
            ->select();
        //echo Db::table('')->getLastSql();
        return $user_result;
    }
    /*
     * 钱数计算
     */
    public static function MoneyList($result,$where,$where_count){
        //总条数
        $int_total_count = count($result);
        //线上支付总金额（扫码成功）
        $card_success_money = Db::table('order_info')->where($where.' and trade_state = 2')->sum('pay_money');
        //扫码失败钱数
        //$card_failure_money = Db::table('order_info')->where($where.' and trade_state = 1')->sum('pay_money');
        //线下交易总金额（出票总钱数）
        $out_total_count = Db::table('under_line')->where($where_count.' and status = 2')->sum('price');
        //线下退票总金额（退票总钱数）
        $back_total_count = Db::table('under_line')->where($where_count.' and status = 1')->sum('price');
        //交易总金额（实际钱数）
        $total_money = $card_success_money + $out_total_count - $back_total_count;
        $data['CardSucMoney']=$card_success_money;
        //$data['CardFaiMoney']=$card_failure_money;
        $data['OutMoney'] = $out_total_count;
        $data['BackMoney'] = $back_total_count;
        $data['TotalMoney'] = $total_money;
        return $data;
    }
}